﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Security;

public class ApplicantMethord
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
    public ApplicantMethord()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public void connClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
    private void connOpen()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }
    private DataTable SelectUser()
    {
        DataTable tb = new DataTable();

        try
        {
            SqlCommand comm = new SqlCommand("SELECT * from SelectUserApplicant", conn);
            SqlDataAdapter da = new SqlDataAdapter(comm);
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception)
        {
        }
        finally
        {
            conn.Close();
        }
        return tb;
    }
    private string CheckUserAdd()
    {
        string sUser = "";
        try
        {
            DataTable tb = new DataTable();
            tb = SelectUser();
            if (tb.Rows.Count > 0)
            {
                string ID = tb.Rows[tb.Rows.Count - 1]["ApplicantID"].ToString();
                char[] cID = ID.ToArray();
                for (int i = (ID.Count() - 1); i >= 0; i--)
                {
                    try
                    {
                        int iu = Convert.ToInt32(cID[i].ToString());
                        if (iu < 9)
                        {
                            iu++;
                            sUser += iu;
                            for (int j = i - 1; j >= 0; j--)
                            {
                                sUser += cID[j].ToString();
                            }
                            break;
                        }
                        else
                        {
                            iu = 0;
                            sUser += iu;
                        }
                    }
                    catch (Exception)
                    {
                        sUser += cID[i].ToString();
                    }
                }
                char[] cUser = sUser.ToArray();
                sUser = "";
                for (int i = (cUser.Count() - 1); i >= 0; i--)
                {
                    sUser += cUser[i].ToString();
                }
            }
            else
            {
                sUser = "A0001";
            }
        }
        catch (Exception)
        {
        }
        finally
        {
            conn.Close();
        }
        return sUser;
    }
    public bool InsertApplicant(string _VacancyID, string _FullName, string _Address, string _Phone, DateTime _BirthOfDate, string _EmailID, string _Education, string _ForeignLanguages, string _WorkExperience)
    {
        bool result = false;
        try
        {
            
            SqlCommand comm = new SqlCommand("EXECUTE CreateNewApplicant @ApplicantID, @VacancyID, @FullName, @Address, @Phone, @BirthOfDate, @EmailID, @Education  , @ForeignLanguages, @WorkExperience, 3", conn);
            comm.Parameters.AddWithValue("@ApplicantID", CheckUserAdd());
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            comm.Parameters.AddWithValue("@FullName", _FullName);
            comm.Parameters.AddWithValue("@Address", _Address);
            comm.Parameters.AddWithValue("@Phone", _Phone);
            comm.Parameters.AddWithValue("@BirthOfDate", _BirthOfDate);
            comm.Parameters.AddWithValue("@EmailID", _EmailID);
            comm.Parameters.AddWithValue("@Education", _Education);
            comm.Parameters.AddWithValue("@ForeignLanguages", _ForeignLanguages);
            comm.Parameters.AddWithValue("@WorkExperience", _WorkExperience);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int iresult = comm.ExecuteNonQuery();
            if (iresult > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        return result;
    }


    public DataTable TypeOfWork()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectTypeJob", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception)
        {
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }
    


    //Page CreateNewApplicant
    public bool CreateNewApplicant(string _User, string _Pass, string _Name, string _Address, string _Phone, string _Date, string _Email)
    {
        bool Result = false;
        try
        {

            string Pass = FormsAuthentication.HashPasswordForStoringInConfigFile(_Pass, "SHA1");
            SqlCommand comm = new SqlCommand("EXECUTE CreateNewAppliacnt @User, @Pass, @Name, @Address, @Phone, @Date, @Email", conn);
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@Pass", Pass);
            comm.Parameters.AddWithValue("@Name", _Name);
            comm.Parameters.AddWithValue("@Address", _Address);
            comm.Parameters.AddWithValue("@Phone", _Phone);
            comm.Parameters.AddWithValue("@Date", _Date);
            comm.Parameters.AddWithValue("@Email", _Email);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }
    public bool CheckEmail(string _email)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("Select EmailID from tb_Applicant", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataReader da = comm.ExecuteReader();
            while (da.Read())
            {
                if (da.GetString(0).Equals(_email))
                {
                    return false;
                }
            }
        }
        catch (Exception)
        {
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return true;
    }
    public bool CheckLoginApplicant(string _User, string _Pass)
    {
        bool Result = false;
        try
        {
            string Pass = FormsAuthentication.HashPasswordForStoringInConfigFile(_Pass, "SHA1");
            SqlCommand comm = new SqlCommand("EXECUTE CheckLoginApplicant @User, @Pass", conn);
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@Pass", Pass);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }

    //Page ApplicantApplyToVacancy
    public SqlDataReader ReturnVacancyOld(string _ID)
    {
        SqlDataReader dr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationVacancyFollowApplicant @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            dr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
        }
        return dr;
    }
    public SqlDataReader ReturnVacancyNew(string _ID)
    {
        SqlDataReader dr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationVacancyApplicant @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            dr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
        }
        return dr;
    }
    public bool ApplicantApplyToVancacy(string _ID, string _VacancyID)
    {
        bool Result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ApplicantApplyToVancacy @ID, @VacancyID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }
    
    //Page ChangeInformationApplicant
    public SqlDataReader ReturnInformationApplicantFollowID(string _User)
    {
        SqlDataReader dr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationApplicantFollowID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _User);
            connOpen();
            dr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
        }
        return dr;
    }

    public bool ChangeApplicant(string _User, string _Name, string _Address, string _Phone, string _Date, string _Email, string _Education, string _Languages, string _Work)
    {
        bool Result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE UpdateApplicantFollowID @User, @Name, @Address, @Phone, @Date, @Email, @Education, @Languages,  @Work", conn);
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@Name", _Name);
            comm.Parameters.AddWithValue("@Address", _Address);
            comm.Parameters.AddWithValue("@Phone", _Phone);
            comm.Parameters.AddWithValue("@Date", _Date);
            comm.Parameters.AddWithValue("@Email", _Email);
            comm.Parameters.AddWithValue("@Education", _Education);
            comm.Parameters.AddWithValue("@Languages", _Languages);
            comm.Parameters.AddWithValue("@Work", _Work);
            connOpen();
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                Result = true;
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }

    //Page Changepassword
    public int ChangepasswordApplicant(string _User, string _Pass)
    {
        int Result = 0;
        try
        {
            string Pass = FormsAuthentication.HashPasswordForStoringInConfigFile(_Pass, "SHA1");
            SqlCommand comm = new SqlCommand("EXECUTE UpdatePasswordApplicant @User, @Pass", conn);
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@Pass", Pass);
            connOpen();
            Result = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }

    //Page VacancyDetails
    public DataTable ReturnInformationVacancy(string _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInformationVacancyApplicant @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }
}
